# Shopping cart related functions
from app.utils.database import get_db_cursor

def get_cart_items(user_id):
    """Get cart items"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("""
            SELECT * FROM shopping_cart WHERE user_id = %s
        """, (user_id,))
        return [dict(row) for row in cursor.fetchall()]
    finally:
        cursor.close()
        conn.close()

def add_to_cart(user_id, category, product_id, quantity):
    """Add item to cart"""
    conn, cursor = get_db_cursor()
    try:
        # First check product stock
        cursor.execute(f"SELECT stock FROM {category} WHERE product_id = %s", (product_id,))
        product = cursor.fetchone()
        if not product:
            raise ValueError("Product does not exist")
        
        # Check if item already exists
        cursor.execute("""
            SELECT * FROM shopping_cart 
            WHERE user_id = %s AND product_id = %s AND category = %s
        """, (user_id, product_id, category))
        existing = cursor.fetchone()
        
        if existing:
            # Calculate new quantity and check if it exceeds stock
            new_quantity = existing['quantity'] + quantity
            if new_quantity > product['stock']:
                raise ValueError(f"Insufficient stock! Current stock: {product['stock']} items, cart has: {existing['quantity']} items, trying to add: {quantity} items")
            
            # Update quantity
            cursor.execute("""
                UPDATE shopping_cart SET quantity = %s
                WHERE user_id = %s AND product_id = %s AND category = %s
            """, (new_quantity, user_id, product_id, category))
        else:
            # Check if quantity exceeds stock for new items
            if quantity > product['stock']:
                raise ValueError(f"Insufficient stock! Current stock: {product['stock']} items, you are trying to add: {quantity} items")
            
            # Add new item
            cursor.execute("""
                INSERT INTO shopping_cart (user_id, product_id, category, quantity)
                VALUES (%s, %s, %s, %s)
            """, (user_id, product_id, category, quantity))
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def update_cart_item(cart_id, user_id, quantity):
    """Update cart item quantity"""
    conn, cursor = get_db_cursor()
    try:
        if quantity <= 0:
            # Delete item
            cursor.execute("""
                DELETE FROM shopping_cart WHERE cart_id = %s AND user_id = %s
            """, (cart_id, user_id))
        else:
            # Get cart item info to check stock
            cursor.execute("""
                SELECT category, product_id FROM shopping_cart 
                WHERE cart_id = %s AND user_id = %s
            """, (cart_id, user_id))
            cart_item = cursor.fetchone()
            
            if not cart_item:
                raise ValueError("Cart item does not exist")
            
            # Check product stock
            cursor.execute(f"SELECT stock FROM {cart_item['category']} WHERE product_id = %s", (cart_item['product_id'],))
            product = cursor.fetchone()
            
            if not product:
                raise ValueError("Product does not exist")
            
            # Ensure quantity doesn't exceed stock
            if quantity > product['stock']:
                raise ValueError(f"Insufficient stock! Current stock: {product['stock']} items, you are trying to set: {quantity} items")
            
            # Update quantity
            cursor.execute("""
                UPDATE shopping_cart SET quantity = %s
                WHERE cart_id = %s AND user_id = %s
            """, (quantity, cart_id, user_id))
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def remove_cart_item(cart_id, user_id):
    """Remove cart item"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("""
            DELETE FROM shopping_cart WHERE cart_id = %s AND user_id = %s
        """, (cart_id, user_id))
        conn.commit()
        return True
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def clear_cart(user_id):
    """Clear cart"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("DELETE FROM shopping_cart WHERE user_id = %s", (user_id,))
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()
